﻿using System;
using System.Linq;
using System.Text;
using Kaidou.Model;
using System.Data;
using System.Data.SqlClient;
using Kaidou.Util;
using System.Collections;
using Kaidou.Model;


namespace Kaidou.Persister
{
    public class TRN_Report_Rate_Persister : Common//zhou--anyuan
    {
        /**
	 * 対象組合員の報告率を取得する。
	 * @param con コネクション
	 * @param union_code 組合員コード
	 * @return Vector 報告率明細のモデル
	 * @throws PersisterSQLException
	 */
        public ArrayList getReportRates(string union_code)
        {
            ArrayList list = new ArrayList();

            StringBuilder pssql = new StringBuilder();

            pssql.Append(" select ");
            pssql.Append(" union_code, ");
            pssql.Append(" seal_year, ");
            pssql.Append(" order_seal_qty, ");
            pssql.Append(" report_seal_qty, ");
            pssql.Append(" report_rate, ");
            pssql.Append(" count_date ");
            pssql.Append(" from ");
            pssql.Append(" trn_report_rate ");
            pssql.Append(" where ");
            pssql.Append(" union_code = @union_code ");
            pssql.Append(" order by ");
            pssql.Append(" seal_year desc ");


            try
            {
                LogManager.info("TRN_Report_Rate_Persister pssql:" + pssql.ToString());

                //SQL実行
                SqlDB db = new SqlDB();
                SqlParameter[] sqlParameter = { new SqlParameter("@union_code", SqlDbType.VarChar, 5) };
                sqlParameter[0].Value = union_code;
                //sqlParameter[1].Value = 0;
                SqlDataReader dataReader = db.GetDataCmd(pssql.ToString(), sqlParameter);

                while (dataReader.Read())
                {
                    TrnReportRate model = new TrnReportRate();
                    TrnReportRateId id = new TrnReportRateId();
                    id.UnionCode = (string)dataReader["union_code"];
                    id.SealYear = (short)dataReader["seal_year"];
                    model.Id = id;
                    model.OrderSealQty = (int)dataReader["order_seal_qty"];
                    model.ReportSealQty = (int)dataReader["report_seal_qty"];
                    model.ReportRate = (float)dataReader["report_rate"];
                    model.CountDate = (DateTime)dataReader["count_date"];
                    list.Add(model);
                }

                db.Close();

            }
            catch (Exception e)
            {
                LogManager.error("TRN_Report_Rate_Persister " + e.Message);
            }
            LogManager.info("TRN_Report_Rate_Persister getReportRates over!!");
            return list;

        }


        /**zhou--anyuan
         * 受注明細を削除する
         * @param con コネクション
         * @return int 削除レコード数
         * @throws PersisterSQLException
         */
        public int delete(SqlDB con)
        {
            int result = 0;

            String pssql = "delete from trn_report_rate ";
            try
            {

                result = con.ExecuteCmd(pssql, null);

            }
            catch (Exception e)
            {
                //Logger.logWrite(TRN_Juchu_DPersister.class.getName(),"E","delete",e);
                throw e;
            }
            //finally {
            //    try {
            //        con.Close();
            //    } catch (Exception e) {
            //        LogManager.fatal("TRN_Juchu_DPersister - delete - PreparedStatement.close" + e.ToString());
            //    }
            //}
            return result;
        }

        /**zhou--anyuan
	 * TRN_Report_Rateテーブルにレコードを挿入します。
	 * @param	con	DBコネクション
	 * @param	model 報告詳細モデル
	 * @return	boolean
	 * @throws	PersisterSQLException
	*/
        public bool insert(SqlDB con, TrnReportRate model)
        {


            StringBuilder sql = new StringBuilder();

            sql.Append(" insert into ");
            sql.Append(" TRN_REPORT_RATE( ");
            sql.Append(" union_code, ");
            sql.Append(" seal_year, ");
            sql.Append(" order_seal_qty, ");
            sql.Append(" report_seal_qty, ");
            sql.Append(" report_rate, ");
            sql.Append(" count_date ");
            sql.Append(" ) ");
            sql.Append(" values ");
            sql.Append(" ( ");
            sql.Append(" @union_code, ");
            sql.Append(" @seal_year, ");
            sql.Append(" @order_seal_qty, ");
            sql.Append(" @report_seal_qty, ");
            sql.Append(" @report_rate, ");
            sql.Append(" @count_date ) ");

            try
            {
                SqlParameter[] parmsU = new SqlParameter[]
            {
                 new SqlParameter("@union_code", SqlDbType.VarChar,10), //
                 new SqlParameter("@seal_year", SqlDbType.SmallInt), //
                 new SqlParameter("@order_seal_qty", SqlDbType.Int), //
                 new SqlParameter("@report_seal_qty", SqlDbType.Int), //
                 new SqlParameter("@report_rate", SqlDbType.Real), //
                 new SqlParameter("@count_date", SqlDbType.DateTime) //
            };
                parmsU[0].Value = model.Id.UnionCode;
                parmsU[1].Value = model.Id.SealYear;
                parmsU[2].Value = model.OrderSealQty;
                parmsU[3].Value = model.ReportSealQty;
                parmsU[4].Value = model.ReportRate;
                parmsU[5].Value = model.CountDate;

                con.ExecuteCmd(sql.ToString(), parmsU);

            }
            catch (Exception e)
            {
                throw e;
            }
            return true;
        }



        /**zhou--anyuan
             * 組合員コードごとに一年間分の報告枚数をカウントする<br>
             * @param con コネクション
             * @param nen 集約対象年
             * @return Hashtable 報告枚数 
             * @throws PersisterSQLException
             */
        public Hashtable getReportSealTotal(SqlDB con, string nen)
        {
            String pssql = null;
            Hashtable hash = null;
            int rscnt = 0;

            try
            {

                pssql = " select union_code,count(seal_no) as seal_qty " + " from " + " report_kensu " + " where yy = @yy " + " group by " + " union_code ";
                /*
                                pssql = " select rs.union_code,rs.seal_no,count(rs.seal_no_id) as seal_no_id "+
                                            " from trn_report_seal rs left join trn_purch_seal ps on rs.seal_no = ps.seal_no "+
                                            " where "+
                                            " ps.union_code = rs.union_code "+
                                            " and rs.seal_no like ? "+
                                            " and rs.union_code = ? "+
                                            " group by "+
                                            " rs.union_code,rs.seal_no ";
                */
                LogManager.debug("TRN_Purch_SealPersister - ### pssql : " + pssql);
                // try
                //{
                SqlParameter[] parmsU = new SqlParameter[]
                            {
                                  new SqlParameter("@yy", SqlDbType.VarChar,2) //
                            };
                parmsU[0].Value = nen.Substring(2);

                SqlDataReader rdr = con.GetLargeDataCmd(pssql, 60, parmsU);

                hash = new Hashtable();
                if (rdr != null)
                {
                    DataTable rs = DBUnit.ConvertDataReaderToDataTable(rdr);
                    foreach (DataRow row in rs.Rows)
                    {
                        hash.Add(DBUnit.GetString(row["union_code"]), DBUnit.GetString(row["seal_qty"]));

                    }
                }

            }
            catch (Exception e)
            {
                LogManager.error("TRN_Purch_SealPersister - getReportSealTotal" + e.ToString());
                throw e;
            }
            //finally {
            //    try {
            //        con.Close();
            //    } catch (Exception e) {
            //        LogManager.fatal("TRN_Purch_SealPersister - getReportSealTotal - DB.close" + e.ToString());
            //    }


            return hash;
        }
    }
}
